﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;
using System.Windows.Forms;

namespace QuanLySieuThi_08520114_08520220
{
    class controller_hoadon
    {
        private SqlCeDataAdapter temp_adapter;
        private DataTable temp_table;
        private SqlCeConnection con = null;


        /*===================  Phương thức thanh toán ====================*/
        public DataTable list_pttt()
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select * From PTTHANHTOAN";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public bool del_pttt(string ma_pttt)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Delete From PTTHANHTOAN Where MaPhuongThucTT = '" + ma_pttt + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool add_pttt(string ten_pttt)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From PTTHANHTOAN Where TenPhuongThucTT ='" + ten_pttt + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Tên Phương Thức Thanh Toán đã có trong database !", "Thông báo");
                        return false;
                    }

                    cmd.CommandText = "Insert Into PTTHANHTOAN (TenPhuongThucTT) VALUES ('" + ten_pttt + "')";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool update_pttt(string ma_pttt, string ten_pttt)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From PTTHANHTOAN Where TenPhuongThucTT ='" + ten_pttt + "' and MaPhuongThucTT != '" + ma_pttt + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Tên Phương Thức Thanh Toán đã có trong database !", "Thông báo");
                        return false;
                    }

                    cmd.CommandText = "Update PTTHANHTOAN Set TenPhuongThucTT = '" + ten_pttt + "' Where MaPhuongThucTT = '" + ma_pttt + "'";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public string get_pttt_id(string ten_pttt)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select MaPhuongThucTT From PTTHANHTOAN where TenPhuongThucTT ='" + ten_pttt + "'";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table.Rows[0]["MaPhuongThucTT"].ToString();

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return "";
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return "";
            }
            return "";
        }
        /*============================================================*/


        /*===================  Hoá Đơn ====================*/
        public DataTable list_hd()
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select * From HOADON";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public DataTable list_cthd(string ma_hd)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select c.*,m.MaMatHang,ch.TenChungLoai,d.TenDonViTinh From CHITIETHOADON c Left join MatHang m on (c.MatHangID = m.MatHangID) Left join CHUNGLOAI ch on (ch.MaChungLoai = c.MaChungLoai) left join DONVITINH d on (d.MaDonViTinh = c.MaDonViTinh)  where HoaDonID = '"+ma_hd+"'";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public DataTable get_ma(string ten_table, string hauto)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select Ma"+hauto+" From "+ ten_table;
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public string get_hd_id(string ma_hd)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select HoaDonID From HOADON where MaHoaDon ='"+ma_hd+"'";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table.Rows[0]["HoaDonID"].ToString();

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return "";
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return "";
            }
            return "";
        }

        public DataTable get_nhanvien(string ma)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select NhanVienID,HoTen From NHANVIEN where MaNhanVien ='" + ma + "'";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public DataTable get_mathang(string ma)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select * From MATHANG m LEFT JOIN CHUNGLOAI c on m.MaChungLoai = c.MaChungLoai Left join DONVITINH d on m.MaDonViTinh = d.MaDonViTinh where MaMatHang ='"+ma+"'";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public bool add_hd(string[] data)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From HOADON Where MaHoaDon ='" + data[0] + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Mã Hoá Đơn đã có trong database !", "Thông báo");
                        return false;
                    }

                    cmd.CommandText = "Insert Into HOADON (MaHoaDon,MaPhuongThucTT,NhanVienID,TenNhanVien,NgayXuatHoaDon,MaSoQuayTT,TongTienPhaiTra,MaSoTheTD) VALUES ('" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "','" + data[5] + "','" + data[6] + "','" + data[7] + "')";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool add_cthd(string[] data)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    string temp = "";
                    for (int i = 0; i < 14; i++)
                    {
                        if (i == 0)
                            temp = "'" + data[0] + "'";
                        else
                            temp = temp + ",'" + data[i] + "'";
                    }
                    sql = "Insert Into CHITIETHOADON (HoaDonID,MatHangID,TenMatHang,SoLuong,GiaBanThuc,VAT,ThanhTien,MaChungLoai,MaDonViTinh,NhaSanXuat,NhaPhanPhoi,GiaNhap,KhuyenMai,GiaTruocKhuyenMai) VALUES (" + temp + ")";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool del_hd(string hd_id)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Delete From CHITIETHOADON Where HoaDonID = '" + hd_id + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "Delete From HOADON Where HoaDonID = '" + hd_id + "'";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }
        /*============================================================*/

    }
}
